package com.scalar.chiptrack.sales.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.operations.process.dao.FinishedGoodsDAO;
import com.scalar.chiptrack.sales.ContactPerson;
import com.scalar.chiptrack.sales.CustomerPartNumber;
import com.scalar.chiptrack.sales.EndUser;
import com.scalar.chiptrack.utils.SqlQueryMappings;

/**
 * Author: Prabhanjan
 * Date: Mar 18, 2004
 * Time: 9:37:55 PM
 */

public class EndUserDAO
{
    public static int saveEndUserDetails ( DBTransaction dbTransaction, EndUser endUser ) throws ScalarException
    {

        Connection conn = null;
        boolean success = false;

        int addressId = -1;

        try
        {


            //if customerId == -1 then vendor is not exists insert the data, if exists then update it
            if ( endUser.getEndUserId () == -1 )
            {
                //System.out.println("CustomerDAO:customer.getCustomerId() == -1");
                if ( endUser == null )
                {
                    //System.out.println("Customer is null here:");
                    endUser = new EndUser ();
                }

                //Insert & Set the default billing address
                endUser.setDefaultBillingAddressId ( CustomerDAO.insertAddress ( dbTransaction,
                                                                                 endUser.getBillingAddress ().getAddressId (),
                                                                                 endUser.getBillingAddress ().getAddress1 (),
                                                                                 endUser.getBillingAddress ().getAddress2 (),
                                                                                 endUser.getBillingAddress ().getAddress3 (),
                                                                                 endUser.getBillingAddress ().getAddress4 (),
                                                                                 endUser.getBillingAddress ().getCity (),
                                                                                 endUser.getBillingAddress ().getState (),
                                                                                 endUser.getBillingAddress ().getZipCode (),
                                                                                 endUser.getBillingAddress ().getCountry (),
                                                                                 endUser.getBillingContactPersonObject().getPhoneNumber (),
                                                                                 endUser.getBillingContactPersonObject ().getFaxNumber (),
                                                                                 endUser.getBillingContactPersonObject ().getEmail (),
                                                                                 endUser.getBillingAddress ().getContactPerson (),
                                                                                 endUser.getBillingContactPersonObject ().getCellNumber (),
                                                                                 true, endUser.getBillingAddress ().getTagName()) );

                endUser.setDefaultShippingAddressId ( CustomerDAO.insertAddress ( dbTransaction,
                                                                                  endUser.getShippingAddress ().getAddressId (),
                                                                                  endUser.getShippingAddress ().getAddress1 (),
                                                                                  endUser.getShippingAddress ().getAddress2 (),
                                                                                  endUser.getShippingAddress ().getAddress3 (),
                                                                                  endUser.getShippingAddress ().getAddress4 (),
                                                                                  endUser.getShippingAddress ().getCity (),
                                                                                  endUser.getShippingAddress ().getState (),
                                                                                  endUser.getShippingAddress ().getZipCode (),
                                                                                  endUser.getShippingAddress ().getCountry (),
                                                                                  endUser.getShippingContactPersonObject ().getPhoneNumber (),
                                                                                  endUser.getShippingContactPersonObject ().getFaxNumber (),
                                                                                  endUser.getShippingContactPersonObject ().getEmail (),
                                                                                  endUser.getShippingAddress ().getContactPerson (),
                                                                                  endUser.getShippingContactPersonObject ().getCellNumber (),
                                                                                  true, endUser.getShippingAddress ().getTagName()) );

                endUser.setContactPerson ( endUser.getBillingAddress ().getContactPerson () );
                endUser = saveEndUser ( dbTransaction, endUser, true );

                ArrayList shippingAdds = endUser.getShippingLocations ();

                for ( int locationNo = 0; locationNo < shippingAdds.size (); locationNo++ )
                {
                    Address address = ( Address ) shippingAdds.get ( locationNo );
                    //Save the location address and return addressId
                    if ( ( address.getAddress1 () != null ) && ( address.getAddress1 ().trim ().equals ( "" ) ) &&
                            ( address.getAddress2 () != null ) && ( address.getAddress2 ().trim ().equals ( "" ) ) &&
                            ( address.getCity () != null ) && ( address.getCity ().trim ().equals ( "" ) ) &&
                            ( address.getState () != null ) && ( address.getState ().trim ().equals ( "" ) ) &&
                            ( address.getZipCode () != null ) && ( address.getZipCode ().trim ().equals ( "" ) ) &&
                            ( address.getCountry () != null ) && ( address.getCountry ().trim ().equals ( "" ) ) &&
                            ( address.getContactPerson () != null ) && ( address.getContactPerson ().trim ().equals ( "" ) ) &&
                            ( address.getContactPersonObject().getCellNumber () != null ) && ( address.getContactPersonObject().getCellNumber ().trim ().equals ( "" ) ) )
                    {

                    }
                    else
                    {
                        addressId = CustomerDAO.insertAddress ( dbTransaction, address.getAddressId (),
                                                                address.getAddress1 (), address.getAddress2 (),
                                                                address.getAddress3 (), address.getAddress4 (),
                                                                address.getCity (), address.getState (),
                                                                address.getZipCode (), address.getCountry (), 
                                                                address.getContactPersonObject().getPhoneNumber (),
                                                                address.getContactPersonObject().getFaxNumber (),
                                                                address.getContactPersonObject().getEmail (),
                                                                address.getContactPerson (), address.getContactPersonObject().getCellNumber (), true, address.getTagName());
                                                                
						CustomerDAO.updateAddressContactPerson ( dbTransaction,  addressId ,address.getLocAddressId() );
						CustomerDAO.updateAddressContactPersonDetails ( dbTransaction,  addressId, address.getContactPerson(),address.getContactPersonObject().getPhoneNumber(),address.getContactPersonObject().getCellNumber(),address.getContactPersonObject().getFaxNumber(),address.getContactPersonObject().getEmail(),"");
                        insertEndUserShippingTable ( dbTransaction, endUser.getEndUserId (), addressId );
                    }

                    // added by basha (07/08/2003)
                    if ( ( address.getDefaultAddress () != null ) && ( address.getDefaultAddress ().equals ( "true" ) ) )
                    {
                        updateEndUserDefaultShippingAddress ( dbTransaction, endUser.getEndUserId (), addressId );
                    }
                    // added by basha (07/08/2003)
                }
            }
            else
            {
                endUser.setContactPerson ( endUser.getBillingAddress ().getContactPerson () );
                endUser = saveEndUser ( dbTransaction, endUser, false );


                /*
                saveCustomerDetailsForBacklog(dbTransaction, customer.getSalesPerson(),
                                customer.getBillingAddress().getContactPerson(),
                                customer.getBillingAddress().getPhoneNumber(),
                                                        customer.getBillingAddress().getFaxNumber(),
                                                        customer.getBillingAddress().getEmail(),
                                customer.getBillingAddress().getAddress1(),
                                                        customer.getBillingAddress().getAddress2(),
                                customer.getBillingAddress().getCity(),
                                                        customer.getBillingAddress().getState(),
                                customer.getBillingAddress().getZipCode(),
                                customer.getBillingAddress().getCountry(),
                                customer.getCustomerId());

                  */
                //Update & Set the default billing address



                addressId = CustomerDAO.insertAddress ( dbTransaction,
                                                        endUser.getDefaultBillingAddressId (),
                                                        endUser.getBillingAddress ().getAddress1 (),
                                                        endUser.getBillingAddress ().getAddress2 (),
                                                        endUser.getBillingAddress ().getAddress3 (),
                                                        endUser.getBillingAddress ().getAddress4 (),
                                                        endUser.getBillingAddress ().getCity (),
                                                        endUser.getBillingAddress ().getState (),
                                                        endUser.getBillingAddress ().getZipCode (),
                                                        endUser.getBillingAddress ().getCountry (),
                                                        endUser.getBillingContactPersonObject ().getPhoneNumber (),
                                                        endUser.getBillingContactPersonObject ().getFaxNumber (),
                                                        endUser.getBillingContactPersonObject ().getEmail (),
                                                        endUser.getBillingAddress ().getContactPerson (),
                                                        endUser.getBillingContactPersonObject ().getCellNumber (),
                                                        false, endUser.getBillingAddress ().getTagName());


                addressId = CustomerDAO.insertAddress ( dbTransaction,
                                                        endUser.getDefaultShippingAddressId (),
                                                        endUser.getShippingAddress ().getAddress1 (),
                                                        endUser.getShippingAddress ().getAddress2 (),
                                                        endUser.getShippingAddress ().getAddress3 (),
                                                        endUser.getShippingAddress ().getAddress4 (),
                                                        endUser.getShippingAddress ().getCity (),
                                                        endUser.getShippingAddress ().getState (),
                                                        endUser.getShippingAddress ().getZipCode (),
                                                        endUser.getShippingAddress ().getCountry (),
                                                        endUser.getShippingContactPersonObject ().getPhoneNumber (),
                                                        endUser.getShippingContactPersonObject ().getFaxNumber (),
                                                        endUser.getShippingContactPersonObject ().getEmail (),
                                                        endUser.getShippingAddress ().getContactPerson (),
                                                        endUser.getShippingContactPersonObject ().getCellNumber (),
                                                        false, endUser.getShippingAddress ().getTagName());

                ArrayList shippingAdds = endUser.getShippingLocations ();
                //Complete the code from here...
                for ( int i = 0; i < shippingAdds.size (); i++ )
                {
                    Address address = ( Address ) shippingAdds.get ( i );
                    //Location is not exists insert it..
                    if ( ( address.getRemove () != null ) && ( address.getRemove ().equals ( "true" ) ) )
                    {
                        EndUserDAO.removeEndUserAddress ( dbTransaction, endUser.getEndUserId (), address.getAddressId () );
                    }
                    else if ( address.getAddressId () == -1 )
                    {
                        if ( ( address.getAddress1 () != null ) && ( address.getAddress1 ().trim ().equals ( "" ) ) &&
                                ( address.getAddress2 () != null ) && ( address.getAddress2 ().trim ().equals ( "" ) ) &&
                                ( address.getCity () != null ) && ( address.getCity ().trim ().equals ( "" ) ) &&
                                ( address.getState () != null ) && ( address.getState ().trim ().equals ( "" ) ) &&
                                ( address.getZipCode () != null ) && ( address.getZipCode ().trim ().equals ( "" ) ) &&
                                ( address.getCountry () != null ) && ( address.getCountry ().trim ().equals ( "" ) ) &&
                                ( address.getContactPerson () != null ) && ( address.getContactPerson ().trim ().equals ( "" ) ) &&
                                ( address.getContactPersonObject().getCellNumber () != null ) && ( address.getContactPersonObject().getCellNumber ().trim ().equals ( "" ) ) )
                        {
                            //Do nothing
                        }
                        else
                        {
                            addressId = CustomerDAO.insertAddress ( dbTransaction,
                                                                    address.getAddressId (),
                                                                    address.getAddress1 (),
                                                                    address.getAddress2 (),
                                                                    address.getAddress3 (),
                                                                    address.getAddress4 (),
                                                                    address.getCity (),
                                                                    address.getState (),
                                                                    address.getZipCode (),
                                                                    address.getCountry (),
                                                                    address.getContactPersonObject().getPhoneNumber (),
                                                                    address.getContactPersonObject().getFaxNumber (),
                                                                    address.getContactPersonObject().getEmail (),
                                                                    address.getContactPerson (),
                                                                    address.getContactPersonObject().getCellNumber (),
                                                                    true, address.getTagName());
                            insertEndUserShippingTable ( dbTransaction, endUser.getEndUserId (), addressId );
							CustomerDAO.updateAddressContactPerson ( dbTransaction,  addressId, address.getLocAddressId());
							CustomerDAO.updateAddressContactPersonDetails ( dbTransaction,  addressId, address.getContactPerson(),address.getContactPersonObject().getPhoneNumber(),address.getContactPersonObject().getCellNumber(),address.getContactPersonObject().getFaxNumber(),address.getContactPersonObject().getEmail(),"");

                        }
                    }
                    else
                    {
                        //Save the location address details for this location
                        addressId = CustomerDAO.insertAddress ( dbTransaction,
                                                                address.getAddressId (),
                                                                address.getAddress1 (),
                                                                address.getAddress2 (),
                                                                address.getAddress3 (),
                                                                address.getAddress4 (),
                                                                address.getCity (),
                                                                address.getState (),
                                                                address.getZipCode (),
                                                                address.getCountry (),
                                                                address.getContactPersonObject().getPhoneNumber (),
                                                                address.getContactPersonObject().getFaxNumber (),
                                                                address.getContactPersonObject().getEmail (),
                                                                address.getContactPerson (),
                                                                address.getContactPersonObject().getCellNumber (),
                                                                false, address.getTagName());
						CustomerDAO.updateAddressContactPersonDetails ( dbTransaction,  addressId, address.getContactPerson(),address.getContactPersonObject().getPhoneNumber(),address.getContactPersonObject().getCellNumber(),address.getContactPersonObject().getFaxNumber(),address.getContactPersonObject().getEmail(),"");
	                                                                
                    }

                    //Update customer default shipping addressId
                    if ( ( address.getDefaultAddress () != null ) && ( address.getDefaultAddress ().equals ( "true" ) ) )
                    {
                        updateEndUserDefaultShippingAddress ( dbTransaction, endUser.getEndUserId (), addressId );
                    }
                }

            }

			            updateEndUserContactPersonDetails ( dbTransaction, endUser.getEndUserId (), endUser.getBillingAddress ().getContactPerson (),
			                                                endUser.getContactPersonObject ().getPhoneNumber (),
			                                                endUser.getContactPersonObject ().getCellNumber (),
			                                                endUser.getContactPersonObject ().getFaxNumber (),
			                                                endUser.getContactPersonObject ().getEmail (),
			                                                endUser.getContactPersonObject ().getAddress () );

			CustomerDAO.updateAddressContactPersonDetails  (dbTransaction, endUser.getDefaultBillingAddressId(),
															endUser.getBillingAddress().getContactPerson(),
															endUser.getBillingContactPersonObject().getPhoneNumber(),
															endUser.getBillingContactPersonObject().getCellNumber(),
															endUser.getBillingContactPersonObject().getFaxNumber(),
															endUser.getBillingContactPersonObject().getEmail(),
															endUser.getBillingContactPersonObject().getAddress());
			CustomerDAO.updateAddressContactPersonDetails  (dbTransaction, endUser.getDefaultShippingAddressId(),
															endUser.getShippingAddress().getContactPerson(),
															endUser.getShippingContactPersonObject().getPhoneNumber(),
															endUser.getShippingContactPersonObject().getCellNumber(),
															endUser.getShippingContactPersonObject().getFaxNumber(),
															endUser.getShippingContactPersonObject().getEmail(),
															endUser.getShippingContactPersonObject().getAddress());


            success = true;
        }
        catch ( ScalarException se )
        {
            // Exception while updating user profile
            // log it
            se.printStackTrace ();
        }


        return endUser.getEndUserId ();
    }

    private static EndUser saveEndUser ( DBTransaction dbTransaction, EndUser endUser,
                                         boolean insert ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int endUserId = -1;
        boolean success = false;


        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            String contactperson = endUser.getContactPerson ();
            if ( "-1".equals ( contactperson ) )
            {
                contactperson = "";
            }
            if ( insert )
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.insertEndUser" ) );
            }
            else
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.updateEndUser" ) );
            }
            pstmt.setInt ( 1, endUser.getCustomerId () );
            pstmt.setString ( 2, endUser.getEndUserName () );
            pstmt.setString ( 3, contactperson );
            pstmt.setInt ( 4, endUser.getDefaultShippingAddressId () );
            pstmt.setInt ( 5, endUser.getDefaultBillingAddressId () );

            if ( !insert )
            {
                pstmt.setInt ( 6, endUser.getEndUserId () );
            }
            pstmt.executeUpdate ();
            pstmt.close ();
            pstmt = null;
            if ( insert )
            {
                pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as end_user_id" );
                rs = pstmt.executeQuery ();
                if ( rs.next () )
                    endUserId = rs.getInt ( "end_user_id" );
                endUser.setEndUserId ( endUserId );
            }

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update End User into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return endUser;
    }

    public static void updateEndUserDefaultShippingAddress ( DBTransaction dbTransaction, int endUserId, int addressId )
            throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "sales.endUser.updateEndUserDefaultShippingAddress" ) );
            cstmt.setInt ( 1, endUserId );
            cstmt.setInt ( 2, addressId );

            cstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not save customer Address into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

    }


    public static void insertEndUserShippingTable ( DBTransaction dbTransaction, int endUserId,
                                                    int addressId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.insertEndUserShippingAddressTable" ) );

            pstmt.setInt ( 1, endUserId );
            pstmt.setInt ( 2, addressId );

            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update End User shipping Address table into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

    }

    public static ArrayList getAllEndUserNames( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList endUsers = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select End_User_ID,E_Name from End_User_Table where Customer_ID = ? ORDER BY E_Name" );
            pstmt.setInt( 1, customerId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                int endUserId = rs.getInt( "End_User_ID" );
                endUsers.add( new LabelValueBean( rs.getString( "E_Name" ), Integer.toString( endUserId ) ) );
            }
        }
        catch( Exception sqlex )
        {
            sqlex.printStackTrace();
            throw new ScalarException( "Could not get End Users from database", sqlex );
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return endUsers;
    }

    public static EndUser getEndUserDetails ( int endUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        EndUser endUser = new EndUser();

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.selectEndUser" ) );
            pstmt.setInt ( 1, endUserId );

            rs = pstmt.executeQuery ();

            if ( rs.next () == false )
            {
                // Customer not found, ???Throw exception
            }
            else
            {
                endUser.setEndUserId ( endUserId );
                endUser.setCustomerId ( rs.getInt ( "Customer_ID" ) );
                endUser.setEndUserName ( rs.getString ( "E_Name" ) );
                endUser.setContactPerson ( rs.getString ( "E_Contact_Person" ) );
                endUser.setDefaultShippingAddressId ( rs.getInt ( "E_Default_Shipping_Address_Id" ) );
                endUser.setDefaultBillingAddressId ( rs.getInt ( "E_Default_Billing_Address_Id" ) );


                Address finalAddress = CustomerDAO.getAddress ( endUser.getDefaultBillingAddressId () );
                String contactPersonName = endUser.getContactPerson ();

				if(endUserId != -1)
				{
					endUser.setShippingLocations(getAllEndUserShippingAddresses(endUserId));
				}
				else
				{
					ArrayList shippingLocs = new ArrayList();
					endUser.setShippingLocations(shippingLocs);
				}
                
                //endUser.setShippingLocations ( getAllEndUserShippingAddresses ( endUserId ) );
                // If condition added by Prabhanjan (17 Jun 2004)
                if( finalAddress != null )
                {
                    String billContactPersonName = finalAddress.getContactPerson();
                    ContactPerson billingContactPerson = null;
                    if( billContactPersonName != null && billContactPersonName.trim().length() != 0 )
                    {
                         billingContactPerson = CustomerDAO.getAddressContactPersonDetails (endUser.getDefaultBillingAddressId() , billContactPersonName);
                         if(billingContactPerson == null )
                         {
                            billingContactPerson  = new ContactPerson();
                         }
                    }
                    else if ( billContactPersonName == null || billContactPersonName.trim().length() == 0 || "-1" .equals(billContactPersonName))
                    {
                        billingContactPerson  = new ContactPerson();
                    }
                    // Added by veeru on 26th April 2004
                    finalAddress.setPhoneNumber(billingContactPerson.getPhoneNumber());
                    finalAddress.setCellNumber(billingContactPerson.getCellNumber());
                    finalAddress.setFaxNumber(billingContactPerson.getFaxNumber());
                    finalAddress.setEmail(billingContactPerson.getEmail());
                    // End of Added by veeru
                    endUser.setBillingAddress ( finalAddress );
                    endUser.setBillingContactPersonObject(billingContactPerson);
                }

                Address shipAddress =  CustomerDAO.getAddress ( endUser.getDefaultShippingAddressId () );

				ContactPerson shippingContactPerson = null;
				String shipContactPersonName = shipAddress.getContactPerson();
				
			   if( shipContactPersonName != null && shipContactPersonName.trim().length() != 0 )
			   {
					shippingContactPerson = CustomerDAO.getAddressContactPersonDetails (endUser.getDefaultShippingAddressId() , shipContactPersonName);
					if(shippingContactPerson == null )
					{
						shippingContactPerson  = new ContactPerson();
					}
			   }
			   else if ( shipContactPersonName == null || shipContactPersonName.trim().length() == 0 || "-1" .equals(shipContactPersonName))
			   {
					shippingContactPerson  = new ContactPerson();
			   }
               // Added by veeru on 26th April 2004
               shipAddress.setPhoneNumber(shippingContactPerson.getPhoneNumber());
               shipAddress.setCellNumber(shippingContactPerson.getCellNumber());
               shipAddress.setFaxNumber(shippingContactPerson.getFaxNumber());
               shipAddress.setEmail(shippingContactPerson.getEmail());
               // End of Added by veeru
               endUser.setShippingAddress ( shipAddress );
			   endUser.setShippingContactPersonObject(shippingContactPerson);
			   
			   ArrayList shippingLocs = endUser.getShippingLocations();
			   ContactPerson allcontactPersons = null;
			   if(shippingLocs != null)
				{
					int shippingLocsSize = shippingLocs.size();
				
					for( int a = 0; a < shippingLocsSize; a++)
					{
						Address address = (Address)shippingLocs.get(a);
						allcontactPersons = CustomerDAO.getAddressContactPersonDetails(address.getAddressId(),address.getContactPerson());
						if(allcontactPersons == null)
						{
							allcontactPersons = new ContactPerson();
						}
						address.setContactPersonObject(allcontactPersons);
					}
				}
            }//else
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Customer Info from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }

        return endUser;

    }


	public static ArrayList getAllEndUserShippingAddresses ( int endUserId ) throws ScalarException
	{
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    ArrayList shippingAddresses = new ArrayList ();
	
	    try
	    {
	        conn = DataSource.getConnection ();
	
	        pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getEndUserShippingAddresses" ) );
	        pstmt.setInt ( 1, endUserId );
	
	        rs = pstmt.executeQuery ();
	
	        Address address = null;
	
	        while ( rs.next () )
	        {
	            address = CustomerDAO.getAddress ( rs.getInt ( "address_id" ) );
	            shippingAddresses.add ( address );
	        }
	
	
	    }
	    catch ( Exception sqlex )
	    {
	        sqlex.printStackTrace ();
	        throw new ScalarException ( "Could not get Shipping Addresses from database", sqlex );
	    }
	    finally
	    {
	        if ( rs != null )
	        {
	            try
	            {
	                rs.close ();
	            }
	            catch ( SQLException excp )
	            {
	            }
	        }
	        if ( pstmt != null )
	        {
	            try
	            {
	                pstmt.close ();
	            }
	            catch ( SQLException excp )
	            {
	            }
	        }
	        if ( conn != null )
	        {
	            try
	            {
	                conn.close ();
	            }
	            catch ( SQLException excp )
	            {
	            }
	        }
	    }
	
	    if ( shippingAddresses.size () == 0 )
	        shippingAddresses = new ArrayList ( 0 );
	
	    return shippingAddresses;
	
	}
    
	public static HashMap getAllContactPersonsForEndUserShippingLocations(int endUserId) throws ScalarException
	{
		 Connection conn = null;
		 PreparedStatement pstmt = null;
		 ResultSet rs = null;
		 HashMap allContactPersons = new HashMap();

		 try {
				 conn = DataSource.getConnection();
	
				 pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.endUser.getShippingAddresses"));
				 pstmt.setInt (1, endUserId);
	
				 rs = pstmt.executeQuery();
	
				 HashMap contactPersons = null;
	
				 while(rs.next()) 
				 {
					 int addressId =  rs.getInt("address_id");
					 contactPersons = CustomerDAO.getAddressContactPersonDetailsMap(addressId);
					 //System.out.println("contactPersons  "+contactPersons.size());
					 allContactPersons.put(""+addressId , contactPersons);
				 }
		 }
		 catch (Exception sqlex) {
			 sqlex.printStackTrace();
			 throw new ScalarException ("Could not get Shipping Addresses from database", sqlex);
		 }
		 finally {
			 if (rs != null) {
				 try { rs.close(); } catch (SQLException excp) {}
			 }
			 if (pstmt != null) {
				 try { pstmt.close(); } catch (SQLException excp) {}
			 }
			 if (conn != null) {
				 try { conn.close(); } catch (SQLException excp) {}
			 }
		 }

		 if(allContactPersons.size() == 0)
		 allContactPersons = new HashMap(0);

		 return allContactPersons;

	}



    public static ContactPerson getEndUserContactPersonDetails ( int endUserId, String contactPerson )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getEndUserContactPersonDetails" ) );

            pstmt.setInt ( 1, endUserId );
            pstmt.setString ( 2, contactPerson );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                contactperson = new ContactPerson ();
                contactperson.setContactPerson ( rs.getString ( "Contact_Person" ) );
                contactperson.setPhoneNumber ( rs.getString ( "Phone_Number" ) );
                contactperson.setCellNumber ( rs.getString ( "Cell_Number" ) );
                contactperson.setFaxNumber ( rs.getString ( "Fax" ) );
                contactperson.setEmail ( rs.getString ( "Email" ) );
                contactperson.setAddress ( rs.getString ( "Address" ) );
            }


            success = true;

        }
        catch ( Exception e )
        {
            e.printStackTrace ();
        }
        finally
        {
            try
            {
                if ( success == true )
                    conn.commit ();
                else
                    conn.rollback ();
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return contactperson;

    }

    public static void updateEndUserContactPersonDetails ( DBTransaction dbTransaction, int endUserId, String contactPerson, String phoneNumber, String cellNumber, String fax, String email, String address ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.updateEndUserContactPersonDetails" ) );

            pstmt.setString ( 1, phoneNumber );
            pstmt.setString ( 2, cellNumber );
            pstmt.setString ( 3, fax );
            pstmt.setString ( 4, email );
            pstmt.setString ( 5, address );
            pstmt.setString ( 6, contactPerson );
            pstmt.setInt ( 7, endUserId );
            pstmt.executeUpdate ();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update contact person info into database", sqle );
        }

        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static boolean isEndUserExists ( String endUserName, int customerId, int endUserId, String newOrExistinEndUser ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection ();

            if( "NewEndUser".equalsIgnoreCase( newOrExistinEndUser ) )
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.checkNewEndUser" ) );
                pstmt.setString ( 1, endUserName );
                pstmt.setInt( 2, customerId );
            }
            else
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.checkExistingEndUsers" ) );
                pstmt.setString ( 1, endUserName );
                pstmt.setInt( 2, customerId );
                pstmt.setInt( 3, endUserId );
            }

            rset = pstmt.executeQuery ();

            if ( rset.next () )
            {
                return true;
            }
            else
            {
                return false;
            }

        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not check End User Name from database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static void deleteEndUserContactPerson ( String contactPersonName, int endUserId , String contactPersonValue) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if ( contactPersonName == null )
        {
            return;
        }
        try
        {
            conn = DataSource.getConnection ();
           /* if("EndUser".equals (contactPersonValue))
            {
	            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.deleteEndUserContactPerson" ) );
            }*/
            //else
			if("Address".equals (contactPersonValue))
			{
				pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.deleteAddressContactPerson" ) );
			}

            pstmt.setString ( 1, contactPersonName );
            pstmt.setInt ( 2, endUserId );
            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not delete ContactPerson information into database", sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static void activateEndUserContactPerson ( String contactPersonName, int endUserId ,String contactPersonValue) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if ( contactPersonName == null )
        {
            return;
        }
        try
        {
            conn = DataSource.getConnection ();
			/* if("EndUser".equals (contactPersonValue))
			 {
	            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.activateEndUserContactPerson" ) );
			 }*/
			 //else
			 if("Address".equals (contactPersonValue))
			 {
				pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.activateAddressContactPerson" ) );
			 }

            pstmt.setString ( 1, contactPersonName );
            pstmt.setInt ( 2, endUserId );
            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not activate the ContactPerson", sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static ArrayList getAllEndUserContactPersonsWithActions ( int endUserId ,String contactPersonValue) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ContactPerson contactperson = null;
        ArrayList contactpersonList = new ArrayList ();

        try
        {

            conn = DataSource.getConnection ();
			/*if( "EndUser".equals(contactPersonValue.trim()) )
			{
            	pstmt = conn.prepareStatement ( "select DISTINCT Contact_Person, Deactivated from End_User_Contact_Persons_Table where End_User_Id = ?" );
			}
			else*/
			if( "Address".equals(contactPersonValue.trim()) )
			{
				pstmt = conn.prepareStatement ( "select DISTINCT Contact_Person, Deactivated from Address_Contact_Persons_Table where Address_Id = ?" );
			}
            pstmt.setInt ( 1, endUserId );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                contactperson = new ContactPerson ();
                contactperson.setContactPerson ( rs.getString ( "Contact_Person" ) );
                contactperson.setDisabled ( rs.getInt ( "Deactivated" ) == 0 ? true : false );
                contactpersonList.add ( contactperson );
            }


        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get ContactPersons from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }

        return contactpersonList;
    }

   // public static void insertEndUserContactPerson ( DBTransaction dbTransaction, int endUserId, String contactperson, String phoneNumber, String cellNumber, String fax, String email, String address ,String contactPersonValue) throws ScalarException
    //added by varma(24jun2004) to send the mails to contact persons
    public static void insertEndUserContactPerson ( DBTransaction dbTransaction, int endUserId, String contactperson, String phoneNumber, String cellNumber, String fax, String email,
                                                    boolean salesAckMail , boolean shipAlerts , boolean proformaInvoice ,
                                                    boolean packingSlip , boolean commerInvoice, String address ,String contactPersonValue) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

			/*if( "EndUser".equals(contactPersonValue.trim()) )
			{
				pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.insertEndUserContactPerson" ) );
			}
			else*/
			if( "Address".equals(contactPersonValue.trim()) )
			{
            	pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.insertAddressContactPerson" ) );
			}

            pstmt.setInt ( 1, endUserId );
            pstmt.setString ( 2, contactperson );
            pstmt.setString ( 3, phoneNumber );
            pstmt.setString ( 4, cellNumber );
            pstmt.setString ( 5, fax );
            pstmt.setString ( 6, email );
            pstmt.setString ( 7, address );
            //added by varma(24jun2004)
            pstmt.setInt ( 8, (salesAckMail == false)     ? 1 : 0 );
            pstmt.setInt ( 9, (shipAlerts == false)       ? 1 : 0 );
            pstmt.setInt ( 10, (proformaInvoice == false) ? 1 : 0 );
            pstmt.setInt ( 11, (packingSlip == false)     ? 1 : 0 );
            pstmt.setInt ( 12, (commerInvoice == false)   ? 1 : 0 );
            pstmt.execute ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not insert contactperson, \"" + contactperson + "\" into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static ArrayList getAllContactPersonsForEndUser ( int endUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList contactPersonsList = new ArrayList ();

        try
        {

            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getAllContactPersonsForEndUser" ) );
            pstmt.setInt ( 1, endUserId );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                contactPersonsList.add ( rs.getString ( "Contact_Person" ) );
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Contact Persons from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return contactPersonsList;
    }

    public static HashMap getContactPersonDetailsMapForEndUser ( int endUserId )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        ContactPerson contactperson = null;
        HashMap contactpersonMap = new HashMap ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getContactPersonDetailsMapForEndUser" ) );
            pstmt.setInt ( 1, endUserId );
            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                contactperson = new ContactPerson ();
                contactperson.setContactPerson ( rs.getString ( "Contact_Person" ) );
                contactperson.setPhoneNumber ( rs.getString ( "Phone_Number" ) );
                contactperson.setCellNumber ( rs.getString ( "Cell_Number" ) );
                contactperson.setFaxNumber ( rs.getString ( "Fax" ) );
                contactperson.setEmail ( rs.getString ( "Email" ) );
                contactperson.setAddress ( rs.getString ( "Address" ) );
                contactpersonMap.put ( rs.getString ( "Contact_Person" ), contactperson );
            }
            success = true;

        }
        catch ( Exception e )
        {
            e.printStackTrace ();
        }
        finally
        {
            try
            {
                if ( success == true )
                    conn.commit ();
                else
                    conn.rollback ();
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return contactpersonMap;
    }

    public static void updateEndUserContactPerson ( DBTransaction dbTransaction, int endUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.updateEndUserContactPerson" ) );
            pstmt.setInt ( 1, endUserId );
            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update contactperson with , \"" + endUserId + "\" into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static ArrayList loadAllMfgPartNumbersWithProductIdsForEndUser ( int endUserId ) throws ScalarException
    {

        Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoodsWithEndUserPartNos ( endUserId ).iterator ();
        ArrayList list = new ArrayList ();

        while ( finishedGoodsList.hasNext () )
        {
            CustomerPartNumber customerParNo = new CustomerPartNumber ();
            FinishedGoods finishedGoods = ( FinishedGoods ) finishedGoodsList.next ();
            String mfgPartNo = finishedGoods.getManufacturingPartNo ();
            int productId = finishedGoods.getProductId ();
            String custpartNumber = finishedGoods.getCustomerPartNumber ();
            String labelNumber = finishedGoods.getLabelNumber() ;
            //System.out.println("productId "+productId +"custpartNumber  "+custpartNumber);


            if ( ( mfgPartNo != null ) && ( mfgPartNo.length () != 0 ) )
            {
                customerParNo.setProductId ( productId );
                customerParNo.setManufacturingPartNo ( mfgPartNo );
                customerParNo.setCustomerPartNo ( custpartNumber );
                customerParNo.setLabelNumber(labelNumber); //Added by varma
                list.add ( customerParNo );
            }

        }

        return list;
    }


    public static void saveEndUserPartNumbers ( int customerid, String[] productIds, String[] customerpartnumbers, String[] labelNumbers ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.deleteEndUserPartNumbers" ) );
            pstmt.setInt ( 1, customerid );
            int cnt = pstmt.executeUpdate ();
            //System.out.println("No of records deleted "+ cnt);
            pstmt.close ();
            pstmt = null;

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.insertEndUserPartNumbers" ) );
            for ( int i = 0; i < productIds.length; i++ )
            {
                // System.out.println("productIds   "+productIds[i] +"customerpartnumbers  "+customerpartnumbers[i].length() );
                //if ( ( productIds[i] != null && productIds[i].length () != 0 ) && ( customerpartnumbers[i] != null && customerpartnumbers[i].length () != 0 ) )
                if((productIds[i] != null && productIds[i].length() != 0) && ((customerpartnumbers[i] != null &&  customerpartnumbers[i].length() != 0 ) || (labelNumbers[i] !=null && labelNumbers[i].length() != 0) ) )
                {
                    pstmt.setInt ( 1, customerid );
                    pstmt.setInt ( 2, Integer.parseInt ( productIds[i] ) );
                    pstmt.setString ( 3, customerpartnumbers[i] );
                    pstmt.setString (4, labelNumbers[i] );
                    pstmt.addBatch ();
                }
            }
            pstmt.executeBatch ();
            success = true;

        }
        catch ( Exception se )
        {
            se.printStackTrace ();
        }
        finally
        {
            try
            {
                if ( success == true )
                    conn.commit ();
                else
                    conn.rollback ();
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static ArrayList loadAllEndUsersForCustomer ( int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList endUsers = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getEndUsersForCustomer" ) );
            pstmt.setInt ( 1, customerId );
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                endUsers.add ( new org.apache.struts.util.LabelValueBean ( rs.getString ( "E_Name" ), Integer.toString ( rs.getInt ( "End_User_ID" ) ) ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End Users from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return endUsers;
    }
    // Prabhanjan, Jul 24, 2004 to load all the End Users to show in the revenue reports search screen.
    public static ArrayList loadAllEndUsers() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList endUsers = new ArrayList ();

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getAllEndUsers" ) );

            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                String e_name = rs.getString ( "E_Name" );
                endUsers.add ( new org.apache.struts.util.LabelValueBean ( e_name, e_name ) );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End Users from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return endUsers;
    }

    public static HashMap loadManfacturingPartNumbersWithEndUserPartNos ( int endUserId ) throws ScalarException
    {

        // Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
        Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoodsWithEndUserPartNos ( endUserId ).iterator ();
        HashMap mapCustPartNos = new HashMap ();
        FinishedGoods finishedGoods = null;
        while ( finishedGoodsList.hasNext () )
        {
            finishedGoods = ( FinishedGoods ) finishedGoodsList.next ();
            String mfgPartNo = finishedGoods.getManufacturingPartNo ();
            String custPartNos = finishedGoods.getCustomerPartNumber ();
            mapCustPartNos.put ( mfgPartNo, custPartNos );
        }

        return mapCustPartNos;
    }

    public static int getEndUserIdByName ( String endUserName, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int endUserId = -1;

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.getEndUserIdByName" ) );
            pstmt.setString ( 1, endUserName );
            pstmt.setInt ( 2, customerId );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {
                endUserId = rs.getInt ( "End_User_ID" );
            }


        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get End User ID from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return endUserId;
    }

    public static void removeEndUserAddress ( DBTransaction dbTransaction, int endUserId, int addressId )
            throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.deleteEndUserAddress" ) );

            pstmt.setInt ( 1, endUserId );
            pstmt.setInt ( 2, addressId );
            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not delete customer Address into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

    }

    // Added one more parameter rdmCustomerId by veeru on 14th May 2004 since for new customer this no is generatad
    public static void updateEndUserCustomerIds ( DBTransaction dbTransaction , int customerId, int rdmCustomerId ) throws ScalarException
     {
         Connection conn = null;
         PreparedStatement pstmt = null;
         boolean success = false;

         try
         {
             if ( dbTransaction == null )
             {
                 conn = DataSource.getConnection ();
             }
             else
             {
                 conn = dbTransaction.getConnection ();
             }

             pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.updateEndUserCustomerIds" ) );
             pstmt.setInt ( 1, customerId );
             pstmt.setInt ( 2, rdmCustomerId );

             int cnt = pstmt.executeUpdate ();
             success = true;

         }
         catch ( Exception se )
         {
             se.printStackTrace ();
         }
         finally
         {
             if ( dbTransaction == null )
             {
                 try
                 {
                     if ( conn != null )
                     {
                         if ( success == true )
                             conn.commit ();
                         else
                             conn.rollback ();
                     }
                 }
                 catch ( SQLException ignore )
                 {
                 }
             }
             if ( pstmt != null )
             {
                 try
                 {
                     pstmt.close ();
                 }
                 catch ( SQLException ignore )
                 {
                 }
             }
             if ( conn != null )
             {
                 try
                 {
                     conn.close ();
                 }
                 catch ( SQLException ignore )
                 {
                 }
             }
         }
     }

    public static void updatePartNumberEndUserIds ( DBTransaction dbTransaction, int endUserId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.endUser.updatePartNumberEndUserIds" ) );
            pstmt.setInt ( 1, endUserId );
            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not update End_User_Part_Numbers with , \"" + endUserId + "\" into database", sqle );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

      }
    public static HashMap loadManfacturingPartNumbersWithEndUserLabelNumbers ( int endUserId ) throws ScalarException
    {

        Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoodsWithEndUserPartNos ( endUserId ).iterator ();
        HashMap mapCustPartNos = new HashMap ();
        FinishedGoods finishedGoods = null;
        while ( finishedGoodsList.hasNext () )
        {
            finishedGoods = ( FinishedGoods ) finishedGoodsList.next ();
            String mfgPartNo = finishedGoods.getManufacturingPartNo ();
            String labelNumber = finishedGoods.getLabelNumber() ;
            mapCustPartNos.put ( mfgPartNo, labelNumber );
        }

        return mapCustPartNos;
    }


}